Walkthrough: Yearly Sales Variance

Create a PQL formula to add to queries to see the yearly sales variance. To review PQL functions, click here.

The syntax of this formula will be:

([measures].[data Sales])-([measures].[data Sales],PrevMember([data].[dateKey year]))

STEP 1

Build a custom member in Formula, using two data points. The first data point should contain the Sales measure. Add a minus sign, and then add the second data point.

STEP 2

To the second data point, add the Sales measure, and then open the dateKey Year hierarchy. Instead of selecting a year from the Elements panel, click the tree icon next to the search tool.

STEP 3

Select Previous Member from the Elements panel.

STEP 4

Click the Pyramid button to preview the formula syntax. Then, save the custom member, or click the Quick Discovery button.

STEP 5

Open a new discovery and add: dateKey Year, Sales, and the new custom member.